﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.Basic;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class WcdjDao
    {
        /// <summary>
        /// 添加一条信息
        /// </summary>
        /// <param name="wcdjEntity"></param>
        /// <returns></returns>
        public int AddEntity(WcdjEntity wcdjEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            //parameterlist.Add(new SqlParameter("@wcdj_id",wcdjEntity.wcdj_id));//这个自增的不用加入吧...
            parameterList.Add(new SqlParameter("@user_id", wcdjEntity.user_id));
            parameterList.Add(new SqlParameter("@wcdj_date", wcdjEntity.wcdj_date));
            parameterList.Add(new SqlParameter("@wcdj_addr", wcdjEntity.wcdj_addr));
            parameterList.Add(new SqlParameter("@wcdj_reason", wcdjEntity.wcdj_reason));
            parameterList.Add(new SqlParameter("@wcdj_lxr", wcdjEntity.wcdj_lxr));
            parameterList.Add(new SqlParameter("@wcdj_dh", wcdjEntity.wcdj_dh));
            parameterList.Add(new SqlParameter("@wcdj_stime", wcdjEntity.wcdj_stime));
            parameterList.Add(new SqlParameter("@wcdj_pretime", wcdjEntity.wcdj_pretime));
            //parameterList.Add(new SqlParameter("@wcdj_etime", wcdjEntity.wcdj_etime));//这个是结束时间在刚开始的时候是不能添加的
            parameterList.Add(new SqlParameter("@wcdj_mark", wcdjEntity.wcdj_mark));

            string sql = "insert into user_wcdj (user_id,wcdj_date,wcdj_addr,wcdj_reason,wcdj_lxr,wcdj_dh,wcdj_stime,wcdj_pretime,wcdj_mark) values" +
                "(@user_id,@wcdj_date,@wcdj_addr,@wcdj_reason,@wcdj_lxr,@wcdj_dh,@wcdj_stime,@wcdj_pretime,@wcdj_mark); " +
                " select @@identity";

            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());


            return int.Parse(id);
        }

        /// <summary>
        /// 更新回来时间
        /// </summary>
        /// <param name="id"></param>
        /// <param name="eTime"></param>
        public void UpdateEtime(string id, String eTime)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@wcdj_id", id));
            parameterList.Add(new SqlParameter("@wcdj_etime", eTime));

            String sql = "update user_wcdj set [wcdj_etime]=@wcdj_etime where [wcdj_id]=@wcdj_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        /// <summary>
        /// 根据用户id和查询时间查询外出状况
        /// </summary>
        /// <param name="id"></param>
        /// <param name="date">精确到每天</param>
        /// <returns></returns>
        public WcdjEntity QueryEntityByIdAndDate(int id, string date)
        {
            WcdjEntity wcdjEntity = null;

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", id));
            parameterList.Add(new SqlParameter("@wcdj_date", date));

            string sql = "select  wcdj_id,user_id,wcdj_date,wcdj_addr,wcdj_reason,wcdj_lxr,wcdj_dh,wcdj_stime,wcdj_pretime,wcdj_etime,wcdj_mark from user_wcdj" +
            "where user_id=@user_id and wcdj_date=@wcdj_date  order by wcdj_id desc";


            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());

            if (null == result) return null;

            wcdjEntity = new WcdjEntity();
            wcdjEntity.wcdj_id = int.Parse(result[0][0]);
            wcdjEntity.user_id = int.Parse(result[0][1]);
            wcdjEntity.wcdj_date = result[0][2];
            wcdjEntity.wcdj_addr = result[0][3];
            wcdjEntity.wcdj_reason = result[0][4];
            wcdjEntity.wcdj_lxr = result[0][5];
            wcdjEntity.wcdj_dh = result[0][6];
            wcdjEntity.wcdj_stime = result[0][7];
            wcdjEntity.wcdj_pretime = result[0][8];
            wcdjEntity.wcdj_etime = result[0][9];
            wcdjEntity.wcdj_mark = result[0][10];
            return wcdjEntity;
        }

        public WcdjEntity QueryEntity(int wcdj_id)
        {
            WcdjEntity wcdjEntity = null;

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@wcdj_id", wcdj_id));

            string sql = "select  wcdj_id,user_id,wcdj_date,wcdj_addr,wcdj_reason,wcdj_lxr,wcdj_dh,wcdj_stime,wcdj_pretime,wcdj_etime,wcdj_mark from user_wcdj" +
                             " where wcdj_id=@wcdj_id  order by wcdj_id desc";


            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());

            if (null == result) return null;

            wcdjEntity = new WcdjEntity();
            wcdjEntity.wcdj_id = int.Parse(result[0][0]);
            wcdjEntity.user_id = int.Parse(result[0][1]);
            wcdjEntity.wcdj_date = result[0][2];
            wcdjEntity.wcdj_addr = result[0][3];
            wcdjEntity.wcdj_reason = result[0][4];
            wcdjEntity.wcdj_lxr = result[0][5];
            wcdjEntity.wcdj_dh = result[0][6];
            wcdjEntity.wcdj_stime = result[0][7];
            wcdjEntity.wcdj_pretime = result[0][8];
            wcdjEntity.wcdj_etime = result[0][9];
            wcdjEntity.wcdj_mark = result[0][10];
            return wcdjEntity;
        }

        /// <summary>
        /// 这个是查某员工全部的外出状况
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable QueryWcdjById(int id)
        {
            string sql = "select  wcdj_id 登记号,user_id 登记人编号,wcdj_date 登记日期,wcdj_addr 外出地点,wcdj_reason 外出事由,wcdj_lxr 联系人,wcdj_dh 电话,wcdj_stime 出门时间,wcdj_pretime 预计返回时间,wcdj_etime 实际返回时间,wcdj_mark 备注 from user_wcdj " +
            " where user_id=@user_id order by wcdj_id desc";

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", id));

            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }
        public DataTable QueryWcdjAll()
        {
            string sql = "select  wcdj_id 登记号,user_id 登记人编号,wcdj_date 登记日期,wcdj_addr 外出地点,wcdj_reason 外出事由,wcdj_lxr 联系人,wcdj_dh 电话,wcdj_stime 出门时间,wcdj_pretime 预计返回时间,wcdj_etime 实际返回时间,wcdj_mark 备注 from user_wcdj " +
            " order by user_id ";

            var result = DBExecuteUtil.querySqlTable(sql);
            return result;
        }
        public DataTable QueryWcdjById(int user_id, bool isreturn)
        {
            if (isreturn)
            {
                string sql = "select  wcdj_id 登记号,user_id 登记人编号,wcdj_date 登记日期,wcdj_addr 外出地点,wcdj_reason 外出事由,wcdj_lxr 联系人,wcdj_dh 电话,wcdj_stime 出门时间,wcdj_pretime 预计返回时间,wcdj_etime 实际返回时间,wcdj_mark 备注 from user_wcdj " +
                " where user_id=@user_id and  wcdj_etime is not null order by wcdj_id desc";

                List<SqlParameter> parameterList = new List<SqlParameter>();
                parameterList.Add(new SqlParameter("@user_id", user_id));

                var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
                return result;
            }
            else
            {
                string sql = "select  wcdj_id 登记号,user_id 登记人编号,wcdj_date 登记日期,wcdj_addr 外出地点,wcdj_reason 外出事由,wcdj_lxr 联系人,wcdj_dh 电话,wcdj_stime 出门时间,wcdj_pretime 预计返回时间,wcdj_etime 实际返回时间,wcdj_mark 备注 from user_wcdj " +
                " where user_id=@user_id and wcdj_etime is null order by wcdj_id desc";

                List<SqlParameter> parameterList = new List<SqlParameter>();
                parameterList.Add(new SqlParameter("@user_id", user_id));

                var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
                return result;
            }
        }

        /// <summary>
        /// 按时间区间查询某人
        /// </summary>
        /// <param name="id"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public DataTable QueryEntityByIdAndDate(string id, string startDate, string endDate)
        {

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", id));

            string sql = "select w.wcdj_id 登记号,j.user_name 登记人名称,j.user_id 登记人编号,w.wcdj_date 登记日期,w.wcdj_addr 外出地点,w.wcdj_reason 外出事由,w.wcdj_lxr 联系人,w.wcdj_dh 电话,w.wcdj_stime 出门时间,w.wcdj_pretime 预计返回时间,w.wcdj_etime 实际返回时间,w.wcdj_mark 备注,w.wcdj_manage 管理记录,w.wcdj_mrgtime 管理时间 from user_wcdj w,jc_user j " +
            " where w.user_id=j.user_id and w.user_id=@user_id and " + "w.wcdj_date>= '" + startDate + "' and  w.wcdj_date<= '" + endDate + "' order by w.wcdj_id desc";//查询语句有待考究,要考虑某月的某人查询该如何查询

            //select  wcdj_id 登记号,user_id 登记人编号,wcdj_date 登记日期,wcdj_addr 外出地点,wcdj_reason 外出事由,wcdj_dh 联系人及电话,wcdj_stime 出门时间,wcdj_pretime 预计用时,wcdj_etime 实际返回时间,wcdj_mark 备注 from user_wcdj
            //where user_id=1 and wcdj_date>= '2012-11-21'  and wcdj_date<= '2012-11-23'

            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }

        /// <summary>
        /// 不选人直接只选开始和结束时间
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable QueryEntityByDate(string startDate, string endDate)
        {
            string sql = "select w.wcdj_id 登记号,j.user_name 登记人名称,j.user_id 登记人编号,w.wcdj_date 登记日期,w.wcdj_addr 外出地点,w.wcdj_reason 外出事由,w.wcdj_lxr 联系人,w.wcdj_dh 电话,w.wcdj_stime 出门时间,w.wcdj_pretime 预计返回时间,w.wcdj_etime 实际返回时间,w.wcdj_mark 备注,w.wcdj_manage 管理记录,w.wcdj_mrgtime 管理时间 from user_wcdj w,jc_user j " +
            " where  w.user_id=j.user_id and w.wcdj_date>= '" + startDate + "' and  w.wcdj_date<= '" + endDate + "' order by w.wcdj_id desc";//查询语句有待考究,要考虑某月的某人查询该如何查询

            //select  w.wcdj_id 登记号,j.user_name 登记人名称,j.user_id 登记人编号,w.wcdj_date 登记日期,w.wcdj_addr 外出地点,w.wcdj_reason 外出事由,w.wcdj_lxr 联系人,w.wcdj_dh 电话,w.wcdj_stime 出门时间,w.wcdj_pretime 预计返回时间,w.wcdj_etime 实际返回时间,w.wcdj_mark 备注 from user_wcdj w,jc_user j where w.user_id=j.user_id order by wcdj_id desc


            var result = DBExecuteUtil.querySqlTable(sql);
            return result;

        }

        /// <summary>
        /// 按时间区间查询某人checked
        /// </summary>
        /// <param name="id"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public DataTable QueryEntityByIdAndDateChecked(string id, string startDate, string endDate)
        {

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", id));

            string sql = "select w.wcdj_id 登记号,j.user_name 登记人名称,j.user_id 登记人编号,w.wcdj_date 登记日期,w.wcdj_addr 外出地点,w.wcdj_reason 外出事由,w.wcdj_lxr 联系人,w.wcdj_dh 电话,w.wcdj_stime 出门时间,w.wcdj_pretime 预计返回时间,w.wcdj_etime 实际返回时间,w.wcdj_mark 备注,w.wcdj_manage 管理记录,w.wcdj_mrgtime 管理时间 from user_wcdj w,jc_user j " +
            " where w.user_id=j.user_id and w.user_id=@user_id and " + "w.wcdj_date>= '" + startDate + "' and  w.wcdj_date<= '" + endDate + "'  and w.wcdj_mrgtime is not null  and w.wcdj_mrgtime<>' ' order by w.wcdj_id desc";//查询语句有待考究,要考虑某月的某人查询该如何查询

            //select  wcdj_id 登记号,user_id 登记人编号,wcdj_date 登记日期,wcdj_addr 外出地点,wcdj_reason 外出事由,wcdj_dh 联系人及电话,wcdj_stime 出门时间,wcdj_pretime 预计用时,wcdj_etime 实际返回时间,wcdj_mark 备注 from user_wcdj
            //where user_id=1 and wcdj_date>= '2012-11-21'  and wcdj_date<= '2012-11-23'

            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }

        /// <summary>
        /// 不选人直接只选开始和结束时间
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable QueryEntityByDateChecked(string startDate, string endDate)
        {
            string sql = "select w.wcdj_id 登记号,j.user_name 登记人名称,j.user_id 登记人编号,w.wcdj_date 登记日期,w.wcdj_addr 外出地点,w.wcdj_reason 外出事由,w.wcdj_lxr 联系人,w.wcdj_dh 电话,w.wcdj_stime 出门时间,w.wcdj_pretime 预计返回时间,w.wcdj_etime 实际返回时间,w.wcdj_mark 备注,w.wcdj_manage 管理记录,w.wcdj_mrgtime 管理时间 from user_wcdj w,jc_user j " +
            " where  w.user_id=j.user_id and w.wcdj_date>= '" + startDate + "' and  w.wcdj_date<= '" + endDate + "'  and w.wcdj_mrgtime is not null  and w.wcdj_mrgtime<>' '  order by w.wcdj_id desc";//查询语句有待考究,要考虑某月的某人查询该如何查询

            //select  w.wcdj_id 登记号,j.user_name 登记人名称,j.user_id 登记人编号,w.wcdj_date 登记日期,w.wcdj_addr 外出地点,w.wcdj_reason 外出事由,w.wcdj_lxr 联系人,w.wcdj_dh 电话,w.wcdj_stime 出门时间,w.wcdj_pretime 预计返回时间,w.wcdj_etime 实际返回时间,w.wcdj_mark 备注 from user_wcdj w,jc_user j where w.user_id=j.user_id order by wcdj_id desc


            var result = DBExecuteUtil.querySqlTable(sql);
            return result;

        }



        /// <summary>
        /// 查询各个外出地点
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable QueryStaffTopTenAddr(int id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", id));

            string sql = "select distinct(wcdj_addr) 外出地点,user_id 用户id,wcdj_id 登记号,wcdj_lxr 联系人,wcdj_dh 电话 from user_wcdj where user_id=@user_id group by user_id,wcdj_addr,wcdj_id,wcdj_id,wcdj_lxr,wcdj_dh";

            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }

        /// <summary>
        /// 通过Id删除数据
        /// </summary>
        /// <param name="id"></param>
        public void DelectById(string id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@wcdj_id", id));
            string sql = "delete from user_wcdj where wcdj_id=@wcdj_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        /// <summary>
        /// 更新管理员管理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="managemessage"></param>
        /// <param name="managetime"></param>
        public void UpdateManagerById(int id, string managemessage, string managetime)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@wcdj_id", id));
            parameterList.Add(new SqlParameter("@wcdj_manage", managemessage));
            parameterList.Add(new SqlParameter("@wcdj_mrgtime", managetime));

            string sql = "update user_wcdj set [wcdj_manage]=@wcdj_manage,[wcdj_mrgtime]=@wcdj_mrgtime where [wcdj_id]=@wcdj_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }
    }
}
